﻿Imports System.Data.OleDb
Imports DTO

Public Class ChiTietSachDAO

    Public Function ThemChiTietSach(ByVal cts As ChiTietSachDTO) As Boolean
        Dim kq As Boolean = True
        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim sql As String = "insert into ChiTietSach(idSach,Tondau,TonCuoi,PhatSinh,DaXoa) values(?,?,?,?,?)"
        Dim cmd As New OleDbCommand(sql, cn)

        cmd.Parameters.Add("@idSach", OleDbType.Integer)
        cmd.Parameters.Add("@Tondau", OleDbType.Integer)
        cmd.Parameters.Add("@TonCuoi", OleDbType.Integer)
        cmd.Parameters.Add("@PhatSinh", OleDbType.WChar)
        cmd.Parameters.Add("@DaXoa", OleDbType.Integer)

        cmd.Parameters("@idSach").Value = cts.LayIDSach
        cmd.Parameters("@Tondau").Value = cts.laytonDau
        cmd.Parameters("@TonCuoi").Value = cts.LayToncuoi
        cmd.Parameters("@PhatSinh").Value = cts.layPhatSinh
        cmd.Parameters("@DaXoa").Value = 0

        Dim n As Integer = 9
        n = cmd.ExecuteNonQuery()
        If n <> 9 Then
            kq = True
        Else
            kq = False
        End If
        cn.Close()

        Return kq
    End Function

    Public Sub CapNhatChiTietSach(ByVal book As ChiTietSachDTO, ByVal sl As Integer)
        Dim kq As Boolean = True
        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim sql As String = "Update ChiTietSach set TonCuoi = ?  where idSach = ?"
        Dim cmd As New OleDbCommand(sql, cn)


        cmd.Parameters.Add("@sl", OleDbType.Integer)
        cmd.Parameters.Add("@idSach", OleDbType.Integer)
        cmd.Parameters("@idSach").Value = book.LayIDSach
        cmd.Parameters("@sl").Value = sl
        cmd.ExecuteNonQuery()
        cn.Close()
    End Sub

    Public Function LayTonCuoi(ByVal s As ChiTietSachDTO) As DataTable
        Dim kq As New DataTable

        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim sql As String
        sql = "select * from ChiTietSach where idSach = ?"
        'B4: Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(sql, cn)
        cmd.Parameters.Add("@idSach", OleDbType.Integer)
        cmd.Parameters("@idSach").Value = s.LayIDSach()
        Dim a As New OleDb.OleDbDataAdapter()
        a.SelectCommand = cmd
        cn.Close()
        a.Fill(kq)
        Return kq
    End Function

    Public Function TonCuoiSachDaTonTai(ByVal book As ChiTietSachDTO, ByVal sl As Integer) As Boolean
        Dim kq As Boolean = True
        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim sql As String = "Update ChiTietSach set TonCuoi = ?  where idSach = ?"
        Dim cmd As New OleDbCommand(sql, cn)

        cmd.Parameters.Add("@sl", OleDbType.Integer)
        cmd.Parameters.Add("@idSach", OleDbType.Integer)
        cmd.Parameters("@idSach").Value = book.LayIDSach
        cmd.Parameters("@sl").Value = sl
        cmd.ExecuteNonQuery()
        Dim i As Integer = 0
        i = cmd.ExecuteNonQuery()
        If i <> 0 Then
            kq = True
        Else
            kq = False
        End If
        Return kq
        cn.Close()
    End Function


    Public Sub DeleteBook(ByVal idSach_Loai As TheLoaiDTO)

        Dim kq As Boolean = True
        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim sql As String
        sql = "Delete from  Sach , ChiTietSach  where Sach.idSach = ChiTietSach.idSach and Sach.idTheLoai= '" & idSach_Loai.LayIdTheLoai & "'  "

        Dim cmd As New OleDbCommand(sql, cn)
        'cmd.Parameters.Add("@Sach.idTheLoaih", OleDbType.Integer)
        'cmd.Parameters("@Sach.idTheLoai").Value = 
        cmd.ExecuteNonQuery()

        cn.Close()
    End Sub

End Class
